package com.yzyz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.yzyz.bean.news.NewsBean;
import com.yzyz.bean.scenic.PictureBean;
import com.yzyz.dao.NewsDao;
import com.yzyz.utils.mapper.NewsRowMapper;

/**
 * 
 * @author David Sun
 * @Date   2010-4-20
 *
 */
public class NewsDaoImpl implements NewsDao {
	private JdbcTemplate jdbcTemplate;
	
	@SuppressWarnings("unchecked")
	@Override
	public List<PictureBean> getAllByNews(int newsId) {
		String sql = "select * from news_picture where ownerId=" + newsId;
		return this.jdbcTemplate.query(sql, new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				PictureBean p = new PictureBean();
				p.setContents(rs.getBytes("contents"));
				p.setId(rs.getInt("id"));
				p.setOwnerId(rs.getInt("ownerId"));
				return p;
			}});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<NewsBean> initTopN(int limit) {
		String sql = "select * from news order by level, date limit 0, " + limit;
		return this.jdbcTemplate.query(sql, new NewsRowMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<NewsBean> paginate(int start, int limit) {
		String sql = "select * from news order by level, date limit " + start + ", " + limit;
		return this.jdbcTemplate.query(sql, new NewsRowMapper());
	}

	@Override
	public int delete(int id) {
		String sql = "delete from news where id=" + id;
		return this.jdbcTemplate.update(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Object get(int id) {
		String sql = "select * from news where id=" + id;
		List<NewsBean> list = this.jdbcTemplate.query(sql, new NewsRowMapper());
		if(list == null || list.size() == 0){
			return null;
		}
		return list.get(0);
	}

	@Override
	public int getCount(int ownerId) {
		String sql = "select count(*) from news";
		return this.jdbcTemplate.queryForInt(sql);
	}

	@Override
	public int save(Object object) {
		final NewsBean news = (NewsBean)object;
		final String sql = "insert into news (name, contents, date, level) values(?,?,?,?)";
		KeyHolder key = new GeneratedKeyHolder();
		
		this.jdbcTemplate.update(new PreparedStatementCreator(){

			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				java.sql.PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, news.getName());
				ps.setString(2, news.getContents());
				ps.setTimestamp(3, new Timestamp(news.getDate().getTime()));
				ps.setInt(4, news.getLevel());
				return ps;
			}}, key);
		return key.getKey().intValue();
	}

	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;

	}

	@Override
	public void update(Object object) {
		final NewsBean news = (NewsBean)object;
		String sql = "update news set name=?, contents=?, level=? where id=?";
		this.jdbcTemplate.update(sql, new Object[]{news.getName(), news.getContents(), news.getLevel(), news.getId()});
	}

	@Override
	public int updateAvailable(int id, boolean available) {
		return 0;
	}

	@Override
	public void savePicture(PictureBean picture) {
		String sql = "insert into news_picture (ownerId, contents) values(?,?)";
		this.jdbcTemplate.update(sql, new Object[]{picture.getOwnerId(), picture.getContents()});
	}

	@Override
	public void deletePicture(int newsId) {
		String sql = "delete from news_picture where ownerId=" + newsId;
		this.jdbcTemplate.update(sql);
		
	}

}
